require 'sqlite3'
$db = nil

def run_query(query, *args)
  return $db.execute(query, *args)
rescue SQLite3::Exception => e
  echo "SQL Exception: #{e.inspect}"
  nil
end

class DBArea
  @@exist_map = {}
  
  def self.ensure(db)
    db.execute("CREATE TABLE IF NOT EXISTS Area(id          INTEGER       PRIMARY KEY 
                                              , num         VARCHAR(64)   NOT NULL
                                              , name        VARCHAR(128)  NOT NULL
                                              , coord_x     INTEGER
                                              , coord_y     INTEGER
                                              , start       INTEGER
                                              , min_level   INTEGER
                                              , max_level   INTEGER
                                               );"
    )
  end
  
  def self.exist(num)
    return @@exist_map[num] if @@exist_map.key?(num)
    id = $db.get_first_value("SELECT id FROM Area WHERE num=?", num)
    @@exist_map[num] = id unless id.nil?
  end
  def self.create(db, num, name, x, y, start = nil)
    return nil unless self.exist(num).nil?
    echo "AREA -- #{num}"
    db.execute("INSERT INTO Area(num, name, coord_x, coord_y, start) VALUES (?, ?, ?, ?,?)",
                                 num, name, x, y, start
    )
    true
  end
end

class DBRoom
  def self.ensure(db)
    db.execute("CREATE TABLE IF NOT EXISTS Room(id          INTEGER       PRIMARY KEY
                                              , name        VARCHAR(128)  NOT NULL
                                              , area_id     INTEGER       NOT NULL
                                              , terrain     VARCHAR(32)   NOT NULL
                                              , coord_x     INTEGER
                                              , coord_y     INTEGER
                                              , coord_id    INTEGER
                                              , coord_cont  INTEGER
                                              , healer      BOOLEAN       DEFAULT 0
                                              , pk          BOOLEAN       DEFAULT 0
                                              , shop        BOOLEAN       DEFAULT 0
                                              , safe        BOOLEAN       DEFAULT 0
                                              , bank        BOOLEAN       DEFAULT 0
                                              , maze        BOOLEAN       DEFAULT 0
                                              , questor     BOOLEAN       DEFAULT 0
                                              , trainer     BOOLEAN       DEFAULT 0
                                              , guild       BOOLEAN       DEFAULT 0
                                              , angry       BOOLEAN       DEFAULT 0
                                              , no_recall   BOOLEAN       DEFAULT 0
                                              , no_portal   BOOLEAN       DEFAULT 0
                                              , FOREIGN KEY (area_id) REFERENCES Area(id)
                                               );"
    )
  end
  
  def self.exist(id); $db.get_first_value("SELECT id FROM Room WHERE id=?", id) end
  def self.create(db, id, name, zone, terrain, details, coord)
    return nil unless self.exist(id).nil?
    area_id = DBArea.exist(zone)
    echo "ROOM -- #{id} (#{zone}|#{area_id})"
    db.execute("INSERT INTO Room (id, name, area_id, terrain, coord_x, coord_y, coord_id, coord_cont
                                , healer, pk, shop
                                , safe, bank, maze
                                , questor, trainer, guild)
                          VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);",
                                  id, name, area_id, terrain, coord['x'], coord['y'], coord['id'], coord['cont'],
                                  details.include?('healer') ? 1 : 0, details.include?('pk') ? 1 : 0, details.include?('shop') ? 1 : 0,
                                  details.include?('safe') ? 1 : 0, details.include?('bank') ? 1 : 0, details.include?('maze') ? 1 : 0,
                                  details.include?('questor') ? 1 : 0, details.include?('trainer') ? 1 : 0, details.include?('guild') ? 1 : 0
    )
    true
  end
end

class DBExit
  def self.ensure(db)
    db.execute("CREATE TABLE IF NOT EXISTS Exit(id        INTEGER   PRIMARY KEY
                                              , from_id   INTEGER   NOT NULL
                                              , to_id     INTEGER   NOT NULL
                                              , cmd       TEXT      NOT NULL
                                              , door      BOOLEAN   NOT NULL
                                              , cost      INTEGER   NOT NULL
                                              , key       INTEGER
                                              , UNIQUE (from_id, cmd)
                                              , FOREIGN KEY (from_id) REFERENCES Room(id)
                                               );"
    )
  end

  def self.exist(from_id, cmd); $db.get_first_value("SELECT id FROM Exit WHERE from_id=? AND cmd=?", from_id, cmd) end
  def self.create(db, from_id, to_id, cmd, door = false, cost = 1)
    return nil unless self.exist(from_id, cmd).nil?
    echo "EXIT -- #{from_id}-#{cmd}-#{to_id}"
    db.execute("INSERT INTO Exit (from_id, to_id, cmd, door, cost) VALUES (?, ?, ?, ?, ?);",
                                  from_id, to_id, cmd, door ? 1 : 0, cost
    )
    true
  end
end

class DBMob
  @@exist_map = {}
  
  def self.ensure(db)
    db.execute("CREATE TABLE IF NOT EXISTS Mob(id       INTEGER   PRIMARY KEY
                                             , name     TEXT      NOT NULL COLLATE NOCASE
                                             , area_id  INTEGER   NOT NULL
                                             , divine   BOOLEAN
                                             , shop     BOOLEAN
                                             , lvl_min  INTEGER
                                             , lvl_max  INTEGER
                                             , FOREIGN KEY (area_id) REFERENCES Area(id)
                                              );"
    )
  end

  def self.exist(name, area_id)
    return @@exist_map[area_id][name] if @@exist_map.key?(area_id) && @@exist_map[area_id].key?(name)
    id = $db.get_first_value("SELECT id FROM Mob WHERE name=? AND area_id=?", name, area_id)
    @@exist_map[area_id] = {} unless @@exist_map.key?(area_id)
    @@exist_map[area_id][name] = id unless id.nil?
  end
  def self.create(db, name, zone, divine, shop)
    area_id = DBArea.exist(zone)
    return nil unless self.exist(name, area_id).nil?
    echo "MOB -- #{name} (#{zone}|#{area_id})"
    db.execute("INSERT INTO Mob (name, area_id, divine, shop) VALUES (?, ?, ?, ?);",
                                 name, area_id, divine ? 1 : 0, shop ? 1 : 0
    )
    true
  end
end

class DBMobRoom
  def self.ensure(db)
    db.execute("CREATE TABLE IF NOT EXISTS MobRoom(id       INTEGER   PRIMARY KEY
                                                 , mob_id   INTEGER   NOT NULL
                                                 , room_id  INTEGER   NOT NULL
                                                 , UNIQUE (mob_id, room_id)
                                                 , FOREIGN KEY (mob_id) REFERENCES Mob(id)
                                                 , FOREIGN KEY (room_id) REFERENCES Room(id)
                                                  );"
    )
  end

  def self.exist(mob_id, room_id); $db.get_first_value("SELECT id FROM MobRoom WHERE mob_id=? AND room_id=?", mob_id, room_id) end
  def self.create(db, mob, zone, room_id)
    area_id = DBArea.exist(zone)
    mob_id = DBMob.exist(mob, area_id)
    return nil unless self.exist(mob_id, room_id).nil?
    echo "MOBROOM -- #{mob} (#{mob_id})"
    db.execute("INSERT INTO MobRoom (mob_id, room_id) VALUES (?, ?);",
                                     mob_id, room_id
    )
    true
  end
end

$db = SQLite3::Database.open 'data.db' if $db.nil?

$db.transaction {|db|
  DBArea.ensure(db)
  DBRoom.ensure(db)
  DBExit.ensure(db)
  DBMob.ensure(db)
  DBMobRoom.ensure(db)
}